package com.hethongviet.job.helpers.database;

import java.util.ArrayList;
import java.util.List;

import com.hethongviet.job.AppMain;
import com.hethongviet.job.models.Comment;
import com.hethongviet.job.models.FileGeneral;
import com.hethongviet.job.models.FileImage;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class CommentSQLite
{
	public static final String TABLE_NAME = "comment";
	public static final String COLUMN_ID_DB = "idDB";
	public static final String COLUMN_ID = "id";
	public static final String COLUMN_TASK_ID = "task_id";
	public static final String COLUMN_CONTENT = "content";
	public static final String COLUMN_SENDER = "sender";
	public static final String COLUMN_DATE_CREATE = "date";
	public static final String COLUMN_IMAGE_NAME = "image_name";
	public static final String COLUMN_ATTACH_NAME = "attach_name";
	public static final String COLUMN_AUDIO_NAME = "audio_name";
	public static final String COLUMN_IS_READ = "is_read";
	public static final String COLUMN_IMAGE_PATH = "image_path";
	public static final String COLUMN_ATTACH_PATH = "attach_path";
	public static final String COLUMN_AUDIO_PATH = "audio_path";
	public static final String COLUMN_IS_SYNC = "is_sync";

	// Database fields
	private SQLiteDatabase _database;
	private MySQLiteHelper _dbHelper;
	private String[] allColumns = { COLUMN_ID_DB, COLUMN_ID, COLUMN_TASK_ID,
			COLUMN_CONTENT, COLUMN_SENDER, COLUMN_DATE_CREATE,
			COLUMN_IMAGE_NAME, COLUMN_ATTACH_NAME, COLUMN_AUDIO_NAME,
			COLUMN_IS_READ, COLUMN_IMAGE_PATH, COLUMN_ATTACH_PATH,
			COLUMN_AUDIO_PATH, COLUMN_IS_SYNC };

	public static final String STRING_COLUMNS = COLUMN_ID_DB
			+ " integer primary key autoincrement, " + COLUMN_ID + " text, "
			+ COLUMN_TASK_ID + " text, " + COLUMN_CONTENT + " text, "
			+ COLUMN_SENDER + " text, " + COLUMN_DATE_CREATE + " text, "
			+ COLUMN_IMAGE_NAME + " text, " + COLUMN_ATTACH_NAME + " text, "
			+ COLUMN_AUDIO_NAME + " text, " + COLUMN_IS_READ + " integer, "
			+ COLUMN_IMAGE_PATH + " text, " + COLUMN_ATTACH_PATH + " text, "
			+ COLUMN_AUDIO_PATH + " text, " + COLUMN_IS_SYNC + " integer";

	public CommentSQLite(Context context)
	{
		_dbHelper = new MySQLiteHelper(context, null, null);
	}

	public void open() throws SQLException
	{
		_database = _dbHelper.getWritableDatabase();
	}

	public void close()
	{
		_dbHelper.close();
	}

	private boolean dataExist(Comment comment, Cursor cursor)
	{
		if (cursor == null)
		{
			return false;
		}
		for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext())
		{
			String id = cursor.getString(cursor.getColumnIndex(COLUMN_ID));
			if (comment.getId().trim().endsWith(id.trim()))
				return true;
		}
		return false;
	}

	/**
	 * @param Comment
	 * @return row id -1: insert fail
	 */
	public long insert(Comment comment)
	{
		open();

		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_ID + " = ?",
				new String[] { comment.getId() });
		// _database.query(TABLE_NAME, allColumns, null, null, null, null,
		// null);

		// Cursor cursor =

		// _database.rawQuery("SELECT * FROM " + TABLE_NAME
		// + " WHERE " + COLUMN_ID + " = \'" + comment.getId() + "\'",
		// null);

		// _database.query(TABLE_NAME, allColumns, COLUMN_ID + " = \'"
		// + comment.getId() + "\'", null, null, null, null);
		// AppMain.debug("insert 1111111111111 number comments: "
		// + cursor.getCount());
		if (!dataExist(comment, cursor))
		{
			AppMain.debug("insert 1111111111111 INSERT");
			/* Insert to database */
			/* Insert new */
			Cursor cursorInsert = _database.rawQuery(
					"INSERT INTO "
							+ TABLE_NAME
							+ "("
							+ COLUMN_ATTACH_NAME
							+ ", "
							+ COLUMN_ATTACH_PATH
							+ ", "
							+ COLUMN_AUDIO_NAME
							+ ", "
							+ COLUMN_AUDIO_PATH
							+ ", "

							+ COLUMN_CONTENT
							+ ", "
							+ COLUMN_DATE_CREATE
							+ ", "
							+ COLUMN_ID
							+ ", "
							+ COLUMN_IMAGE_NAME
							+ ", "

							+ COLUMN_IMAGE_PATH
							+ ", "
							+ COLUMN_SENDER
							+ ", "
							+ COLUMN_TASK_ID
							+ ", "
							+ COLUMN_IS_READ
							+ ", "

							+ COLUMN_IS_SYNC
							+ ") values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",

					new String[] {

							""
									+ ((comment.getFileAttachment() == null) ? ""
											: getStringValue(comment.getFileAttachment().getName())),

							""
									+ ((comment.getFileAttachment() == null) ? ""
											: getStringValue(comment.getFileAttachment().getPath())),

							""
									+ ((comment.getFileAudio() == null) ? ""
											: getStringValue(comment.getFileAudio().getName())),

							""
									+ ((comment.getFileAudio() == null) ? ""
											: getStringValue(comment.getFileAudio().getPath())),

							comment.getContent(),
							comment.getDateCreate(),
							comment.getId(),

							""
									+ ((comment.getFileImage() == null) ? ""
											: getStringValue(comment.getFileImage().getName())),

							""
									+ ((comment.getFileImage() == null) ? ""
											: getStringValue(comment.getFileImage().getPath())),

							comment.getSender(), comment.getTaskId(),
							comment.isRead() ? "1" : "0",
							comment.isSyncData() ? "1" : "0" }

			);
			cursorInsert.moveToFirst();
			if (!cursorInsert.isAfterLast())
				return cursorInsert.getLong(cursorInsert.getColumnIndex(COLUMN_ID_DB));

			cursorInsert.close();
		}
		else
		{
			AppMain.debug("insert 1111111111111 update");
			/* Update to database */
			Cursor cursorUpdate = _database.rawQuery(
					"UPDATE " + TABLE_NAME + " SET " + COLUMN_ATTACH_NAME
							+ " = ?, " + COLUMN_ATTACH_PATH + " = ?, "
							+ COLUMN_AUDIO_NAME + " = ?, " + COLUMN_AUDIO_PATH
							+ " = ?, " + COLUMN_CONTENT + " = ?, "
							+ COLUMN_DATE_CREATE + " = ?, " + COLUMN_ID
							+ " = ?, "

							+ COLUMN_IMAGE_NAME + " = ?, " + COLUMN_IMAGE_PATH
							+ " = ?, " + COLUMN_SENDER + " = ?, "
							+ COLUMN_TASK_ID + " = ?, " + COLUMN_IS_READ
							+ " = ?, " + COLUMN_IS_SYNC + " = ? " + "WHERE "
							+ COLUMN_ID_DB + " = " + comment.getIdDB(),

					new String[] {

							""
									+ ((comment.getFileAttachment() == null) ? ""
											: getStringValue(comment.getFileAttachment().getName())),

							""
									+ ((comment.getFileAttachment() == null) ? ""
											: getStringValue(comment.getFileAttachment().getPath())),

							""
									+ ((comment.getFileAudio() == null) ? ""
											: getStringValue(comment.getFileAudio().getName())),

							""
									+ ((comment.getFileAudio() == null) ? ""
											: getStringValue(comment.getFileAudio().getPath())),

							comment.getContent(),
							comment.getDateCreate(),
							comment.getId(),
							""
									+ ((comment.getFileImage() == null) ? ""
											: getStringValue(comment.getFileImage().getName())),

							""
									+ ((comment.getFileImage() == null) ? ""
											: getStringValue(comment.getFileImage().getPath())),

							comment.getSender(), comment.getTaskId(),
							comment.isRead() ? "1" : "0",
							comment.isSyncData() ? "1" : "0" }

			);
			cursorUpdate.moveToFirst();
			if (!cursorUpdate.isAfterLast())
				return cursorUpdate.getLong(cursorUpdate.getColumnIndex(COLUMN_ID_DB));

			cursorUpdate.close();
		}
		cursor.close();
		close();
		return -1;
	}

	private String getStringValue(String string)
	{
		return (string == null) ? "" : string;
	}

	public void updateRead(Comment comment)
	{
		open();

		AppMain.debug("1111111111111 update read = " + comment.isRead());
		/* Update to database */

		ContentValues contentValues = new ContentValues();
		contentValues.put(COLUMN_IS_READ, (comment.isRead() ? "1" : "0"));
		_database.update(TABLE_NAME, contentValues, COLUMN_ID + " = \'"
				+ comment.getId() + "\'", null);

		// do not work
		// _database.rawQuery("UPDATE " + TABLE_NAME + " SET "
		// + COLUMN_IS_READ + " = ?" + " WHERE " + COLUMN_ID + " = ?;", new
		// String[] { (comment.isRead() ? "1" : "0"), comment.getId() });
		// _database.rawQuery("UPDATE " + TABLE_NAME + " SET " + COLUMN_IS_READ
		// + " = \'" + (comment.isRead() ? "1" : "0") + "\' WHERE "
		// + COLUMN_ID + " = \'" + comment.getId() + "\'", null);

		close();
	}

	public void delete(Comment comment)
	{
		open();
		System.out.println("Comment deleted with id: " + comment.getIdDB());
		_database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE "
				+ COLUMN_ID_DB + " = " + comment.getIdDB() + ";", null);
		close();
	}

	public void deleteAll()
	{
		open();
		System.out.println("Comment deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null);
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1;", null);
		_database.delete(TABLE_NAME, "1", null);
		close();
	}

	public void deleteAllByTask(String taskId)
	{
		open();
		System.out.println("Comment deleted all");
		// _database.rawQuery("DELETE FROM " + TABLE_NAME, null);
		// _database.rawQuery("DELETE FROM " + TABLE_NAME + " WHERE 1;", null);
		_database.delete(TABLE_NAME, COLUMN_ID + " = \'" + taskId + "\'", null);
		close();
	}

	public List<Comment> getAll()
	{
		open();
		List<Comment> list = new ArrayList<Comment>();

		Cursor cursor = _database.query(TABLE_NAME, allColumns, null, null,
				null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			Comment comment = cursorToComment(cursor);
			list.add(comment);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		close();
		return list;
	}

	public List<Comment> getAllByTask(String taskId)
	{
		open();
		List<Comment> list = new ArrayList<Comment>();

		Cursor cursor = _database.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE " + COLUMN_TASK_ID + " = ?", new String[] { taskId });
		cursor.moveToFirst();

		while (!cursor.isAfterLast())
		{
			Comment comment = cursorToComment(cursor);
			list.add(comment);
			cursor.moveToNext();
		}

		cursor.close();
		close();
		return list;
	}

	private Comment cursorToComment(Cursor cursor)
	{
		Comment comment = new Comment();
		comment.setFileAttachment(new FileGeneral(
				cursor.getString(cursor.getColumnIndex(COLUMN_ATTACH_NAME)),
				null,
				cursor.getString(cursor.getColumnIndex(COLUMN_ATTACH_PATH))));

		comment.setFileAudio(new FileGeneral(
				cursor.getString(cursor.getColumnIndex(COLUMN_AUDIO_NAME)),
				null,
				cursor.getString(cursor.getColumnIndex(COLUMN_AUDIO_PATH))));

		comment.setContent(cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT)));
		comment.setDateCreate(cursor.getString(cursor.getColumnIndex(COLUMN_DATE_CREATE)));
		comment.setId(cursor.getString(cursor.getColumnIndex(COLUMN_ID)));
		comment.setIdDB(cursor.getLong(cursor.getColumnIndex(COLUMN_ID_DB)));
		comment.setFileImage(new FileImage(null,
				cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_NAME)),
				null,
				cursor.getString(cursor.getColumnIndex(COLUMN_IMAGE_PATH))));
		comment.setRead(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_READ)) == 1);
		comment.setSyncData(cursor.getInt(cursor.getColumnIndex(COLUMN_IS_SYNC)) == 1);
		comment.setSender(cursor.getString(cursor.getColumnIndex(COLUMN_SENDER)));
		comment.setTaskId(cursor.getString(cursor.getColumnIndex(COLUMN_TASK_ID)));
		return comment;
	}

	public final boolean isTableExist()
	{
		open();

		boolean isExist = false;
		Cursor cursor = _database.rawQuery(
				"select DISTINCT tbl_name from sqlite_master where tbl_name = '"
						+ TABLE_NAME + "'", null);
		if (cursor != null && cursor.getCount() > 0)
			isExist = true;

		cursor.close();
		close();

		return isExist;
	}
}
